<?php
class pro_tagihan_listrik_model extends MY_Model {
	var $model = 'pro_tagihan_listrik_model';
	
	function GetJSON()
	{
		if ($this->input->post('set')=='filter'){
			$bulan_dari = $this->input->post('bulan_dari');
			$bulan_sampai = $this->input->post('bulan_sampai');
			$tahun = $this->input->post('tahun');
			$status_bayar = $this->input->post('status_bayar');

			$ls_sql	= "	SELECT	a.id_tagihan_listrik,a.no_tenant,a.id_lokasi,a.no_tagihan_listrik,a.nama_tenant,a.no_toko,
								a.jenis_usaha,a.tanggal,a.kwh_awal_meter,
								a.kwh_akhir_meter,a.kwh_terpakai,a.tarif_per_kwh,
								a.jumlah_pemakaian,a.mcb,a.biaya_beban,a.jumlah_biaya_beban,
								a.total_tagihan,a.lokasi_tempat,a.no_bukti_pembayaran,
								TO_CHAR(a.tgl_bayar,'DD/MM/YYYY') tgl_bayar,
								a.status_bayar, b.nama_bulan, b.id_bulan,
								SUBSTR(a.tanggal, 4, INSTR(a.tanggal, '-')+2) AS tahun,
								SUBSTR(a.tanggal, 0, INSTR(a.tanggal, '-')-1) AS bulan,
								concat(b.nama_bulan,REPLACE(SUBSTR(a.tanggal, 4, INSTR(a.tanggal, '-')+2), '-',b.nama_bulan)) as periode_pembayaran
						FROM	pro_tagihan_listrik a
						LEFT JOIN pro_nama_bulan b ON a.id_bulan = b.id_bulan
						WHERE	(SUBSTR(a.tanggal, 4, INSTR(a.tanggal, '-')+2) = '$tahun' AND SUBSTR(a.tanggal, 0, INSTR(a.tanggal, '-')-1) >='$bulan_dari') AND
								(SUBSTR(a.tanggal, 4, INSTR(a.tanggal, '-')+2) = '$tahun' AND SUBSTR(a.tanggal, 0, INSTR(a.tanggal, '-')-1) <='$bulan_sampai') AND
								a.status_bayar = '$status_bayar'  AND
								(UPPER(a.no_tagihan_listrik) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.nama_tenant) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.no_toko) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.jenis_usaha) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.lokasi_tempat) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.tanggal) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.kwh_awal_meter) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.kwh_akhir_meter) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.kwh_terpakai) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.tarif_per_kwh) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.jumlah_pemakaian) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.mcb) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.biaya_beban) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.jumlah_biaya_beban) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.total_tagihan) LIKE '%".strtoupper(post('keyword'))."%') 
								ORDER BY ".(post('sort')=='' ? 'a.no_tagihan_listrik' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));	
			return json_encode($this->paging($ls_sql) );
		} /*else if($this->input->post('set_b')=='filter_status'){
			$status_bayar_b = $this->input->post('status_bayar_b');	
			$ls_sql	= "	SELECT	no_tagihan_listrik,nama_tenant,no_toko,
								jenis_usaha,tanggal,kwh_awal_meter,
								kwh_akhir_meter,kwh_terpakai,tarif_per_kwh,
								jumlah_pemakaian,mcb,biaya_beban,jumlah_biaya_beban,
								total_tagihan,lokasi_tempat,no_bukti_pembayaran,
								TO_CHAR(tgl_bayar,'DD/MM/YYYY') tgl_bayar,
								status_bayar
						FROM	pro_tagihan_listrik
						WHERE	status_bayar = '$status_bayar_b' AND
								(UPPER(no_tagihan_listrik) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(nama_tenant) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(no_toko) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(jenis_usaha) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(lokasi_tempat) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(tanggal) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(kwh_awal_meter) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(kwh_akhir_meter) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(kwh_terpakai) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(tarif_per_kwh) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(jumlah_pemakaian) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(mcb) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(biaya_beban) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(jumlah_biaya_beban) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(total_tagihan) LIKE '%".strtoupper(post('keyword'))."%') 
								ORDER BY ".(post('sort')=='' ? 'no_tagihan_listrik' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
			return json_encode($this->paging($ls_sql) );
		} */else {
			$ls_sql	= "	SELECT	a.id_tagihan_listrik,a.no_tenant,a.id_lokasi,a.no_tagihan_listrik,a.nama_tenant,a.no_toko,
								a.jenis_usaha,a.tanggal,a.kwh_awal_meter,
								a.kwh_akhir_meter,a.kwh_terpakai,a.tarif_per_kwh,
								a.jumlah_pemakaian,a.mcb,a.biaya_beban,a.jumlah_biaya_beban,
								a.total_tagihan,a.lokasi_tempat,a.no_bukti_pembayaran,
								TO_CHAR(a.tgl_bayar,'DD/MM/YYYY') tgl_bayar,
								a.status_bayar, b.nama_bulan, b.id_bulan,
								SUBSTR(a.tanggal, 4, INSTR(a.tanggal, '-')+2) AS tahun,
								SUBSTR(a.tanggal, 0, INSTR(a.tanggal, '-')-1) AS bulan,
								concat(b.nama_bulan,REPLACE(SUBSTR(a.tanggal, 4, INSTR(a.tanggal, '-')+2), '-',b.nama_bulan)) as periode_pembayaran
						FROM	pro_tagihan_listrik a
						LEFT JOIN pro_nama_bulan b ON a.id_bulan = b.id_bulan
						WHERE	(UPPER(a.no_tagihan_listrik) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.nama_tenant) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.no_toko) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.jenis_usaha) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.lokasi_tempat) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.tanggal) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.kwh_awal_meter) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.kwh_akhir_meter) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.kwh_terpakai) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.tarif_per_kwh) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.jumlah_pemakaian) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.mcb) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.biaya_beban) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.jumlah_biaya_beban) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(a.total_tagihan) LIKE '%".strtoupper(post('keyword'))."%') 
								ORDER BY ".(post('sort')=='' ? 'a.no_tagihan_listrik' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
			return json_encode($this->paging($ls_sql) );
		}
	}

	function GetJSON2()
	{
		$ls_sql	= "	SELECT	id_tagihan_listrik,no_tenant,id_lokasi,no_tagihan_listrik,nama_tenant,no_toko,
							jenis_usaha,tanggal,kwh_awal_meter,
							kwh_akhir_meter,kwh_terpakai,tarif_per_kwh,
							jumlah_pemakaian,mcb,biaya_beban,jumlah_biaya_beban,
							total_tagihan,lokasi_tempat,no_bukti_pembayaran,
							TO_CHAR(tgl_bayar,'DD/MM/YYYY') tgl_bayar,status_bayar
					FROM	pro_tagihan_listrik a
					WHERE	(UPPER(kwh_awal_meter) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(kwh_akhir_meter) LIKE '%".strtoupper(post('keyword'))."%') 
							ORDER BY ".(post('sort')=='' ? 'kwh_awal_meter' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));	
		return json_encode($this->paging($ls_sql) );
	}

	function GetCetak($as_no_tagihan_listrik)
	{
		$result1 = $this->db->query(" 	SELECT	a.id_tagihan_listrik,a.no_tenant,a.id_lokasi,a.no_tagihan_listrik,a.nama_tenant,a.no_toko,
												a.jenis_usaha,a.tanggal,a.kwh_awal_meter,
												a.kwh_akhir_meter,a.kwh_terpakai,a.tarif_per_kwh,
												a.jumlah_pemakaian,a.mcb,a.biaya_beban,a.jumlah_biaya_beban,
												a.total_tagihan,a.lokasi_tempat,a.no_bukti_pembayaran,
												TO_CHAR(a.tgl_bayar,'DD/MM/YYYY') tgl_bayar,
												a.status_bayar, b.nama_bulan, b.id_bulan,
												SUBSTR(a.tanggal, 4, INSTR(a.tanggal, '-')+2) AS tahun,
												SUBSTR(a.tanggal, 0, INSTR(a.tanggal, '-')-1) AS bulan,
												concat(b.nama_bulan,REPLACE(SUBSTR(a.tanggal, 4, INSTR(a.tanggal, '-')+2), '-',b.nama_bulan)) as periode_pembayaran
										FROM	pro_tagihan_listrik a
										LEFT JOIN pro_nama_bulan b ON a.id_bulan = b.id_bulan
										WHERE	a.no_tagihan_listrik = '$as_no_tagihan_listrik' ");

		
		return $result1->row_array();
		
	}

	/*function GetCetak($as_no_tagihan_sewa)
	{
		$result1 = $this->db->query(" 	SELECT	a.id_tagihan_sewa,a.no_tagihan_sewa,a.nama_tenant,a.no_toko,
												a.jenis_usaha,a.lokasi_tempat,a.harga_sewa,
												a.tanggal_pembayaran,a.status_bayar,
												a.keterangan,TO_CHAR(a.tanggal_bayar,'DD/MM/YYYY') tanggal_bayar,
												a.no_bukti_pembayaran, b.nama_bulan, b.id_bulan,
												SUBSTR(a.tanggal_pembayaran, 4, INSTR(a.tanggal_pembayaran, '-')+2) AS tahun,
												SUBSTR(a.tanggal_pembayaran, 0, INSTR(a.tanggal_pembayaran, '-')-1) AS bulan,
												concat(b.nama_bulan,REPLACE(SUBSTR(a.tanggal_pembayaran, 4, INSTR(a.tanggal_pembayaran, '-')+2), '-',b.nama_bulan)) as periode_pembayaran
										FROM	pro_tagihan_sewa a
										LEFT JOIN pro_nama_bulan b ON a.id_bulan = b.id_bulan
										WHERE	a.no_tagihan_sewa = '$as_no_tagihan_sewa' ");

		
		return $result1->row_array();
		
	}*/
		
	function Add($arr_data){
		$this->db->insert('pro_tagihan_listrik', $arr_data);
	}

	function UpdateStatus($arr_data, $arr_rule){
		$this->db->update('pro_pemakaian_listrik', $arr_data, $arr_rule);
	}
	
	function Update($arr_data, $arr_rule){
		$this->db->update('pro_tagihan_listrik', $arr_data, $arr_rule);
	}
	
	function GetSingle($as_no_tagighan_listrik){
		$result = $this->db->query("	SELECT	a.id_tagihan_listrik,a.no_tenant,a.id_lokasi,a.no_tagihan_listrik,a.nama_tenant,a.no_toko,
												a.jenis_usaha,a.tanggal,a.kwh_awal_meter,
												a.kwh_akhir_meter,a.kwh_terpakai,a.tarif_per_kwh,
												a.jumlah_pemakaian,a.mcb,a.biaya_beban,a.jumlah_biaya_beban,
												a.total_tagihan,a.lokasi_tempat,a.no_bukti_pembayaran,
												TO_CHAR(a.tgl_bayar,'DD/MM/YYYY') tgl_bayar,a.status_bayar,a.id_pemakaian,
												concat(b.nama_bulan,REPLACE(SUBSTR(a.tanggal, 4, INSTR(a.tanggal, '-')+2), '-',b.nama_bulan)) as periode_pembayaran
										FROM	pro_tagihan_listrik a
										LEFT JOIN pro_nama_bulan b ON a.id_bulan = b.id_bulan
										WHERE no_tagihan_listrik = '$as_no_tagighan_listrik'");
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	function GetSingleView($as_no_tagighan_listrik)
	{
		$result = $this->db->query("SELECT	a.id_tagihan_listrik,a.no_tenant,a.id_lokasi,a.no_tagihan_listrik,a.nama_tenant,a.no_toko,
											a.jenis_usaha,a.tanggal,a.kwh_awal_meter,
											a.kwh_akhir_meter,a.kwh_terpakai,a.tarif_per_kwh,
											a.jumlah_pemakaian,a.mcb,a.biaya_beban,a.jumlah_biaya_beban,
											a.total_tagihan,a.lokasi_tempat,a.no_bukti_pembayaran,
											TO_CHAR(a.tgl_bayar,'DD/MM/YYYY') tgl_bayar,a.status_bayar,a.id_pemakaian,
											concat(b.nama_bulan,REPLACE(SUBSTR(a.tanggal, 4, INSTR(a.tanggal, '-')+2), '-',b.nama_bulan)) as periode_pembayaran
									FROM	pro_tagihan_listrik a
									LEFT JOIN pro_nama_bulan b ON a.id_bulan = b.id_bulan
									WHERE no_tagihan_listrik = '$as_no_tagighan_listrik'");
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}

	function DeleteSingle($arr_rule)
	{
		try {
			if(	intval($this->get_data("SELECT COUNT(1) FROM pro_tagihan_listrik WHERE no_tagihan_listrik = '".$arr_rule['no_tagihan_listrik']."'"))<=0)
			{
				return $this->error_delete.' [id: '.$arr_rule['no_tagihan_listrik']."]<br />";
			}else{
				$this->db->delete('pro_tagihan_listrik', array('no_tagihan_listrik' => $arr_rule['no_tagihan_listrik']));
				return '';
			}
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}
}
?>